package cn.iocoder.boot.springai001.tools;

import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.iocoder.boot.springai001.tools.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.ai.tool.annotation.ToolParam;

import java.util.List;

/**
 * 请求 API 工具类
 */
@Slf4j
public class RequestApiTools {

    private static final String AUTH_TOKEN = "a530da8927a64a079eb3af73e4c65503";
    private static final String BASE_URL = "http://localhost:48080/admin-api/text2sql";

    // ==================== 原有的问数工具 ====================

//    @Tool(description = "问数工具，主要作用是将用户查询问题转换成SQL并执行SQL返回执行结果")
    public String text2sql(@ToolParam(description = "查询问题") String query) {
        Text2SqlAgentQueryRespVO text2sqlBean = str2Text2SqlAgentQueryRespVO(query);
        return text2sqlBean.getResult();
    }

    private Text2SqlAgentQueryRespVO str2Text2SqlAgentQueryRespVO(String query) {
        String url = BASE_URL + "/agent/query";
        JSONObject params = new JSONObject();
        params.set("query", query);
        params.set("includeHistory", false);
        return JSONUtil.toBean(post(url, params), Text2SqlAgentQueryRespVO.class);
    }

    // ==================== Text2SQL 工具集 ====================

    /**
     * 工具1：查询重写
     * 优化用户问题，消除歧义，将相对时间转换为具体时间
     */
    @Tool(description = "查询重写工具，用于优化用户问题，消除歧义，将相对时间（如今天、本月）转换为具体时间")
    public String rewriteQuery(@ToolParam(description = "用户原始问题") String query) {
        String url = BASE_URL + "/tool/rewrite-query";
        
        RewriteQueryReqVO reqVO = RewriteQueryReqVO.builder()
                .query(query)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        RewriteQueryRespVO respVO = JSONUtil.toBean(post(url, params), RewriteQueryRespVO.class);
        
        if (respVO.getChanged()) {
            return String.format("已重写问题：\n原始问题：%s\n重写后：%s", 
                    respVO.getOriginalQuery(), respVO.getRewrittenQuery());
        } else {
            return String.format("问题无需重写：%s", respVO.getRewrittenQuery());
        }
    }

    /**
     * 工具2：智能识别表
     * 根据问题自动识别相关的数据库表
     */
    @Tool(description = "智能识别表工具，根据用户问题自动识别需要查询的数据库表，优先使用语义路由，失败则使用相似度匹配")
    public String identifyTables(@ToolParam(description = "用户问题") String query) {
        String url = BASE_URL + "/tool/identify-tables";
        
        IdentifyTablesReqVO reqVO = IdentifyTablesReqVO.builder()
                .query(query)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        IdentifyTablesRespVO respVO = JSONUtil.toBean(post(url, params), IdentifyTablesRespVO.class);
        
        if (respVO.getSuccess()) {
            return String.format("识别到相关表（方法：%s）：%s", 
                    respVO.getMethod(), String.join(", ", respVO.getTables()));
        } else {
            return "未能识别到相关表";
        }
    }

    /**
     * 工具3：生成SQL
     * 根据问题和表生成SQL语句
     */
    @Tool(description = "生成SQL工具，根据用户问题和相关表生成SQL语句，保证MySQL 5.7兼容性")
    public String generateSql(
            @ToolParam(description = "用户问题") String query,
            @ToolParam(description = "相关表列表，多个表用逗号分隔") String tables) {
        String url = BASE_URL + "/tool/generate-sql";
        
        // 将逗号分隔的字符串转换为列表
        List<String> tableList = List.of(tables.split(","));
        
        GenerateSqlReqVO reqVO = GenerateSqlReqVO.builder()
                .query(query)
                .tables(tableList)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        GenerateSqlRespVO respVO = JSONUtil.toBean(post(url, params), GenerateSqlRespVO.class);
        
        if (respVO.getSuccess()) {
            StringBuilder result = new StringBuilder();
            result.append("生成的SQL：\n").append(respVO.getSql());
            if (!respVO.getWarnings().isEmpty()) {
                result.append("\n警告：").append(String.join("; ", respVO.getWarnings()));
            }
            return result.toString();
        } else {
            return "SQL生成失败";
        }
    }

    /**
     * 工具4：执行SQL
     * 安全地执行SQL查询并返回结果
     */
    @Tool(description = "执行SQL工具，安全地执行SQL查询（只允许SELECT语句）并返回结果")
    public String executeSql(@ToolParam(description = "SQL语句") String sql) {
        String url = BASE_URL + "/tool/execute-sql";
        
        ExecuteSqlReqVO reqVO = ExecuteSqlReqVO.builder()
                .sql(sql)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        ExecuteSqlRespVO respVO = JSONUtil.toBean(post(url, params), ExecuteSqlRespVO.class);
        
        if (respVO.getSuccess()) {
            return String.format("查询成功（耗时：%dms，记录数：%d）：\n%s", 
                    respVO.getExecutionTime(), respVO.getRecordCount(), respVO.getResult());
        } else {
            return String.format("查询失败：%s", respVO.getErrorMessage());
        }
    }

    /**
     * 工具5：分析结果
     * 使用AI分析查询结果，生成自然语言描述和数据洞察
     */
    @Tool(description = "分析结果工具，使用AI分析查询结果，生成自然语言描述、图表推荐和数据洞察")
    public String analyzeResult(
            @ToolParam(description = "查询结果（JSON字符串）") String result,
            @ToolParam(description = "用户问题") String query,
            @ToolParam(description = "SQL语句") String sql) {
        String url = BASE_URL + "/tool/analyze-result";
        
        AnalyzeResultReqVO reqVO = AnalyzeResultReqVO.builder()
                .result(result)
                .query(query)
                .sql(sql)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        AnalyzeResultRespVO respVO = JSONUtil.toBean(post(url, params), AnalyzeResultRespVO.class);
        
        if (respVO.getSuccess()) {
            StringBuilder analysis = new StringBuilder();
            analysis.append("分析结果：\n");
            analysis.append("描述：").append(respVO.getNaturalDescription()).append("\n");
            analysis.append("图表建议：").append(respVO.getChartRecommendation()).append("\n");
            if (respVO.getInsights() != null && !respVO.getInsights().isEmpty()) {
                analysis.append("洞察：\n");
                for (String insight : respVO.getInsights()) {
                    analysis.append("  - ").append(insight).append("\n");
                }
            }
            return analysis.toString();
        } else {
            return "结果分析失败";
        }
    }

    /**
     * 工具6：修复SQL
     * 自动修复SQL语法错误
     */
    @Tool(description = "修复SQL工具，自动修复SQL语法错误，优先使用快速修复，复杂错误使用LLM修复")
    public String fixSql(
            @ToolParam(description = "原始SQL语句") String sql,
            @ToolParam(description = "错误信息") String errorMessage,
            @ToolParam(description = "用户问题（可选）") String userQuery,
            @ToolParam(description = "表结构信息（可选）") String tableSchema) {
        String url = BASE_URL + "/tool/fix-sql";
        
        FixSqlReqVO reqVO = FixSqlReqVO.builder()
                .sql(sql)
                .errorMessage(errorMessage)
                .userQuery(userQuery)
                .tableSchema(tableSchema)
                .build();
        
        JSONObject params = JSONUtil.parseObj(JSONUtil.toJsonStr(reqVO));
        FixSqlRespVO respVO = JSONUtil.toBean(post(url, params), FixSqlRespVO.class);
        
        if (respVO.getSuccess()) {
            return String.format("SQL已修复（方法：%s）：\n原始：%s\n修复后：%s\n说明：%s", 
                    respVO.getFixMethod(), respVO.getOriginalSql(), 
                    respVO.getFixedSql(), respVO.getChanges());
        } else {
            return String.format("SQL修复失败：%s", respVO.getChanges());
        }
    }

    // ==================== 通用请求方法 ====================

    private JSONObject post(String url, JSONObject params) {
        HttpRequest request = HttpRequest.post(url)
                .header("authorization", "Bearer " + AUTH_TOKEN)
                .header("Content-Type", "application/json")
                .body(params.toString());

        try (HttpResponse response = request.execute()) {
            if (response.isOk()) {
                log.info("请求成功，URL：{}，响应：{}", url, response.body());
                JSONObject responseBody = JSONUtil.parseObj(response.body());
                // 处理 CommonResult 包装的响应
                if (responseBody.containsKey("data")) {
                    return responseBody.getJSONObject("data");
                }
                return responseBody;
            } else {
                log.error("请求失败，URL：{}，状态码：{}，响应：{}", url, response.getStatus(), response.body());
                String format = String.format("{\"error\": \"请求失败\", \"statusCode\": %d, \"message\": \"%s\"}",
                        response.getStatus(), response.body());
                return JSONUtil.parseObj(format);
            }
        } catch (Exception e) {
            log.error("请求异常，URL：{}", url, e);
            String format = String.format("{\"error\": \"请求异常\", \"message\": \"%s\"}", e.getMessage());
            return JSONUtil.parseObj(format);
        }
    }

}
